from typing import Optional
from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

data_my_safe_07 = APIRouter(prefix="/phone", tags=["app接口"], dependencies=[Depends(oauth2_scheme)])


class EnvSafetyAlarmSearch(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    fbegdate: str = Field("2000-01-01", description="开始日期(yyyy-MM-dd)，默认'2000-01-01'")
    fenddate: str = Field("2000-01-01", description="结束日期(yyyy-MM-dd)，默认'2000-01-01'")
    fpointNo: str = Field("", description="点位编号")
    boxFequipmentbarcode: str = Field("", description="箱设备条码")
    cabFequipmentbarcode: str = Field("", description="柜设备条码")
    fairitem: str = Field("", description="监测项目")
    flevelno: str = Field("", description="危险等级编号")


@data_my_safe_07.get("/getEnvironmentAlarm2", summary="环境安全--报警记录接口")
def get_environment_alarm2(env: EnvSafetyAlarmSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_tc_cabwarring a"
    sql = sql + " left join a_ts_point b on a.fpoint_no = b.fpoint_no"
    sql = sql + " left join a_ts_site j on j.fsite_no = b.fsite_no "
    sql = sql + " left join lims.i_ts_cabinet c on c.fequipmentbarcode = a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse d on d.fequipmentbarcode = a.fboxbarcode"
    sql = sql + " left join s_ts_airitem e on e.fairitem = a.fairitem"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=c.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join s_ts_danger_level i on i.flevelno = a.flevelno"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fwarringdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
    param["ftakedateEnd"] = env.fenddate
    param["ftakedateStart"] = env.fbegdate
    if env.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = env.fsiteNo
    if env.fpointNo != "":
        sql = sql + " and a.fpoint_no= :fpointNo "
        param["fpointNo"] = env.fpointNo
    if env.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = env.boxFequipmentbarcode
    if env.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= ：cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = env.cabFequipmentbarcode
    if env.fairitem != "":
        sql = sql + " and a.fairitem= :fairitem "
        param["fairitem"] = env.fairitem
    if env.flevelno != "":
        sql = sql + " and a.flevelno= :flevelno "
        param["flevelno"] = env.flevelno
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select to_char(fwarringdate,'yyyy-mm-dd hh24:mi:ss') fwarringdate,fwarringno,j.fsite_no,j.fsite_name,b.fpoint_name "
    sql = sql + " ,g.fequipmenttag fconname ,c.fequipmenttag fcabname,d.fequipmenttag fboxname"
    sql = sql + " ,e.fariname,fcurnum,fdescribe,i.flevelname"
    sql = sql + " from i_tc_cabwarring a"
    sql = sql + " left join a_ts_point b on a.fpoint_no = b.fpoint_no"
    sql = sql + " left join a_ts_site j on j.fsite_no = b.fsite_no "
    sql = sql + " left join lims.i_ts_cabinet c on c.fequipmentbarcode = a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse d on d.fequipmentbarcode = a.fboxbarcode"
    sql = sql + " left join s_ts_airitem e on e.fairitem = a.fairitem"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=c.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join s_ts_danger_level i on i.flevelno = a.flevelno"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fwarringdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
    param["ftakedateEnd"] = env.fenddate
    param["ftakedateStart"] = env.fbegdate
    if env.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = env.fsiteNo
    if env.fpointNo != "":
        sql = sql + " and a.fpoint_no= :fpointNo "
        param["fpointNo"] = env.fpointNo
    if env.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = env.boxFequipmentbarcode
    if env.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= ：cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = env.cabFequipmentbarcode
    if env.fairitem != "":
        sql = sql + " and a.fairitem= :fairitem "
        param["fairitem"] = env.fairitem
    if env.flevelno != "":
        sql = sql + " and a.flevelno= :flevelno "
        param["flevelno"] = env.flevelno
    if env.order == "desc":
        sql = sql + " order by  fwarringdate desc "
    else:
        sql = sql + " order by  fwarringdate asc "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = env.limit
    param["offset"] = env.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fwarringdate'] = data[key][0]
        d1['fwarringno'] = data[key][1]
        d1['fsiteNo'] = data[key][2]
        d1['fsiteName'] = data[key][3]
        d1['fpointName'] = data[key][4]
        d1['fconname'] = data[key][5]
        d1['fcabname'] = data[key][6]
        d1['fboxname'] = data[key][7]
        d1['fariname'] = data[key][8]
        d1['fcurnum'] = data[key][9]
        d1['fdescribe'] = data[key][10]
        d1['flevelname'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": env.limit, "page": env.pageIndex,
                 "pages": (count + env.limit - 1) // env.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class EnvSafetyAlarmSearch(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    fbegdate: str = Field("2000-01-01", description="开始日期(yyyy-MM-dd)，默认'2000-01-01'")
    fenddate: str = Field("2000-01-01", description="结束日期(yyyy-MM-dd)，默认'2000-01-01'")
    fpointNo: str = Field("", description="点位编号")
    boxFequipmentbarcode: str = Field("", description="箱设备条码")
    cabFequipmentbarcode: str = Field("", description="柜设备条码")
    fairitem: str = Field("", description="监测项目（如温湿度、气体浓度）")
    flevelno: str = Field("", description="危险等级编号")


@data_my_safe_07.get("/getEnvironmentAlarm", summary="环境安全--报警记录接口")
def get_environment_alarm(env: EnvSafetyAlarmSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select to_char(fwarringdate,'yyyy-mm-dd hh24:mi:ss') fwarringdate,fwarringno,j.fsite_no,j.fsite_name,b.fpoint_name "
    sql = sql + " ,g.fequipmenttag fconname ,c.fequipmenttag fcabname,d.fequipmenttag fboxname"
    sql = sql + " ,e.fariname,fcurnum,fdescribe,i.flevelname"
    sql = sql + " from i_tc_cabwarring a"
    sql = sql + " left join a_ts_point b on a.fpoint_no = b.fpoint_no"
    sql = sql + " left join a_ts_site j on j.fsite_no = b.fsite_no "
    sql = sql + " left join lims.i_ts_cabinet c on c.fequipmentbarcode = a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse d on d.fequipmentbarcode = a.fboxbarcode"
    sql = sql + " left join s_ts_airitem e on e.fairitem = a.fairitem"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=c.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join s_ts_danger_level i on i.flevelno = a.flevelno"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fwarringdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
    param["ftakedateEnd"] = env.fenddate
    param["ftakedateStart"] = env.fbegdate
    if env.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = env.fsiteNo
    if env.fpointNo != "":
        sql = sql + " and a.fpoint_no= :fpointNo "
        param["fpointNo"] = env.fpointNo
    if env.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = env.boxFequipmentbarcode
    if env.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = env.boxFequipmentbarcode
    if env.fairitem != "":
        sql = sql + " and a.fairitem= :fairitem "
        param["fairitem"] = env.fairitem
    if env.flevelno != "":
        sql = sql + " and a.flevelno= :flevelno "
        param["flevelno"] = env.flevelno
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select to_char(fwarringdate,'yyyy-mm-dd hh24:mi:ss') fwarringdate,fwarringno,j.fsite_no,j.fsite_name,b.fpoint_name "
    sql = sql + " ,g.fequipmenttag fconname ,c.fequipmenttag fcabname,d.fequipmenttag fboxname"
    sql = sql + " ,e.fariname,fcurnum,fdescribe,i.flevelname"
    sql = sql + " from i_tc_cabwarring a"
    sql = sql + " left join a_ts_point b on a.fpoint_no = b.fpoint_no"
    sql = sql + " left join a_ts_site j on j.fsite_no = b.fsite_no "
    sql = sql + " left join lims.i_ts_cabinet c on c.fequipmentbarcode = a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse d on d.fequipmentbarcode = a.fboxbarcode"
    sql = sql + " left join s_ts_airitem e on e.fairitem = a.fairitem"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=c.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join s_ts_danger_level i on i.flevelno = a.flevelno"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fwarringdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
    param["ftakedateEnd"] = env.fenddate
    param["ftakedateStart"] = env.fbegdate
    if env.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = env.fsiteNo
    if env.fpointNo != "":
        sql = sql + " and a.fpoint_no= :fpointNo "
        param["fpointNo"] = env.fpointNo
    if env.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = env.boxFequipmentbarcode
    if env.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = env.boxFequipmentbarcode
    if env.fairitem != "":
        sql = sql + " and a.fairitem= :fairitem "
        param["fairitem"] = env.fairitem
    if env.flevelno != "":
        sql = sql + " and a.flevelno= :flevelno "
        param["flevelno"] = env.flevelno
    if env.order == "desc":
        sql = sql + " order by  fwarringdate desc "
    else:
        sql = sql + " order by fwarringdate asc "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = env.limit
    param["offset"] = env.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fwarringdate'] = data[key][0]
        d1['fwarringno'] = data[key][1]
        d1['fsiteNo'] = data[key][2]
        d1['fsiteName'] = data[key][3]
        d1['fpointName'] = data[key][4]
        d1['fconname'] = data[key][5]
        d1['fcabname'] = data[key][6]
        d1['fboxname'] = data[key][7]
        d1['fariname'] = data[key][8]
        d1['fcurnum'] = data[key][9]
        d1['fdescribe'] = data[key][10]
        d1['flevelname'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": env.limit, "page": env.pageIndex,
                 "pages": (count + env.limit - 1) // env.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
